﻿/*
 ******************************************************************
 *      
 * 域名空间　	: GreatB2C.DALSQLServer
 * 类　名　称	: Dictionary.cs
 * 类　功　能	: 数据操作实现层
 * 类　流　程    :                             
 * 运用算法　	:                             
 * 编　写　人	: WHL             
 * 编写日期　	: 2011-3-28
 * 最近更新日期　:
 * 版　　本　	: V1.0                           
 * 描　　述　	: Descript of Class                                                 
 *                                        
 ******************************************************************
*/

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

using GreatB2C.Model;
using GreatB2C.DALFactory;
using GreatB2C.DALProfile;
using GreatB2C.Config;
using GreatB2C.Utility;

namespace GreatB2C.DALSQLServer
{
    /// <summary>
    /// 类描述：
    /// </summary>
    [Serializable]
    public class Dictionary : DbBase, IDictionary
    {
        #region implement methods

        /// <summary>
        /// 获取参数参数列表
        /// </summary>
        /// <param name="ac"></param>
        /// <returns></returns>
        private SqlParameter[] GetParameters(GreatB2C.Model.Dictionary ac)
        {
            int i = 0;
            SqlParameter[] param = new SqlParameter[ 5 ];

            param[i] = new SqlParameter("@FieldID", SqlDbType.Int, 4);
            param[i++].Value = ac.FieldID;

            param[i] = new SqlParameter("@Title", SqlDbType.NVarChar, 50);
            param[i++].Value = ac.Title;

            param[i] = new SqlParameter("@TableName", SqlDbType.NVarChar, 30);
            param[i++].Value = ac.TableName;

            param[i] = new SqlParameter("@FieldName", SqlDbType.NVarChar, 30);
            param[i++].Value = ac.FieldName;

            param[i] = new SqlParameter("@FieldValue", SqlDbType.NText);
            param[i++].Value = ac.FieldValue;

            return param;
        }

        /// <summary>
        /// 检查是否有重复记录。
        /// 注意：这里要手动修改一下，需要加入次主键做为第二条件（主要是为了修改操作）
        /// </summary>
        /// <param name="objModel">对应的数据映射类</param>
        /// <returns>操作结果：true-成功 false-失败</returns>
        public bool IsExists(GreatB2C.Model.Dictionary objModel)
        {
            string Sql = "SELECT Count(*) "
                            + " FROM "
                            + "[dbo].[" + Prefix + "Dictionary]"
                            + " WHERE "
                            + "	[FieldID] = @FieldID"
                //+" and 次主键=@次主键"
                            ;

            try
            {
                SqlParameter[] param = GetParameters(objModel);

                int count = Convert.ToInt32(DbHelper.ExecuteScalar(CommandType.Text, Sql, param).ToString());
                if (count > 0)
                {
                    return true;
                }

                return false;
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                return false;
            }
        }

        /// <summary>
        /// 检索出表中所有记录	
        /// </summary>
        /// <param name="aryResult">返回对象数据集合</param>
        /// <returns>0-操作成功 , errnum-操作失败，返回错误数</returns>
        public DataTable ListDictionary()
        {
            DataTable dtData = new DataTable();
            string Sql = "SELECT "
                            + "[FieldID],"
                            + "[Title],"
                            + "[TableName],"
                            + "[FieldName],"
                            + "[FieldValue]"
                            + " FROM "
                            + "[dbo].[" + Prefix + "Dictionary]";

            try
            {
                return DbHelper.ExecuteTable(CommandType.Text, Sql, null);
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                return null;
            }
        }

        /// <summary>
        /// 根据表中主键ID查询记录信息
        /// </summary>
        /// <param name="objModel">相应的对象映射模型</param>
        /// <param name="DesModel">输出的对象映射模型</param>
        /// <returns>0-操作成功 , errnum-操作失败，返回错误数</returns>
        public GreatB2C.Model.Dictionary ListByID(GreatB2C.Model.Dictionary objModel)
        {
            string Sql = "SELECT * "
                            + " FROM "
                            + "[dbo].[" + Prefix + "Dictionary]"
                            + " WHERE "
                            + "	[FieldID] = @FieldID";

            try
            {
                SqlParameter[] param = GetParameters(objModel);

                using (DataTable dtData = DbHelper.ExecuteTable(CommandType.Text, Sql, param))
                {
                    if (dtData.Rows.Count > 0)
                    {
                        GreatB2C.Model.Dictionary modClass = new GreatB2C.Model.Dictionary();

                        modClass.FieldID = Convert.ToInt32(dtData.Rows[0]["FieldID"].ToString());
                        modClass.Title = dtData.Rows[0]["Title"].ToString();
                        modClass.TableName = dtData.Rows[0]["TableName"].ToString();
                        modClass.FieldName = dtData.Rows[0]["FieldName"].ToString();
                        modClass.FieldValue = dtData.Rows[0]["FieldValue"].ToString();

                        return modClass;
                    }
                }

                return null;
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                return null;
            }
        }

        /// <summary>
        /// 新增一条新记录到表中
        /// </summary>
        /// <param name="objModel">相应的对象映射模型</param>
        /// <returns>0-操作成功 , errnum-操作失败，返回错误数</returns>
        public bool Insert(GreatB2C.Model.Dictionary objModel)
        {
            string Sql = "INSERT INTO "
                        + "[dbo].[" + Prefix + "Dictionary]"
                        + "("
                        + "[Title],"
                        + "[TableName],"
                        + "[FieldName],"
                            + "[FieldValue]"
                        + ") VALUES ("
                        + "@Title,"
                        + "@TableName,"
                        + "@FieldName,"
                            + "@FieldValue"
                        + ")";

            try
            {
                SqlParameter[] param = GetParameters(objModel);

                int affect = DbHelper.ExecuteNonQuery(CommandType.Text, Sql, param);

                if (affect == 1)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                return false;
            }
        }

        /// <summary>
        /// 根据主键ID更新指定记录
        /// </summary>
        /// <param name="objModel">相应的对象映射模型</param>
        /// <returns>0-操作成功 , errnum-操作失败，返回错误数</returns>
        public bool Update(GreatB2C.Model.Dictionary objModel)
        {
            string Sql = "UPDATE "
                        + "[dbo].[" + Prefix + "Dictionary]"
                        + " SET "
                        + "[Title] = @Title,"
                        + "[TableName] = @TableName,"
                        + "[FieldName] = @FieldName,"
                            + "[FieldValue] = @FieldValue"
                        + " WHERE "
                        + "[FieldID] = @FieldID"
                        ;

            try
            {
                SqlParameter[] param = GetParameters(objModel);

                int affect = DbHelper.ExecuteNonQuery(CommandType.Text, Sql, param);

                if (affect == 1)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                return false;
            }
        }

        /// <summary>
        /// 根据主键删除指定记录
        /// </summary>
        /// <param name="objModel">相应的对象映射模型</param>
        /// <returns>0-操作成功 , errnum-操作失败，返回错误数</returns>
        public bool Delete(GreatB2C.Model.Dictionary objModel)
        {
            string Sql = "DELETE FROM "
                            + "[dbo].[" + Prefix + "Dictionary]"
                            + " WHERE "
                            + "[FieldID] = @FieldID"
                            ;

            try
            {
                SqlParameter[] param = GetParameters(objModel);

                int affect = DbHelper.ExecuteNonQuery(CommandType.Text, Sql, param);

                if (affect == 1)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                return false;
            }
        }

        /// <summary>
        /// 根据ID列表批量删除记录
        /// 数据格式：1,2,3,4,5
        /// </summary>
        /// <param name="strList">ID列表</param>
        /// <returns>0-操作成功 , errnum-操作失败，返回错误数</returns>
        public bool DeleteList(string strList)
        {
            string Sql = "DELETE FROM "
                            + "[dbo].[" + Prefix + "Dictionary]"
                            + " WHERE "
                            + "[FieldID] in ('" + strList + "')"
                            ;

            try
            {
                int affect = DbHelper.ExecuteNonQuery(CommandType.Text, Sql, null);

                if (affect == 1)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                return false;
            }
        }

        /// <summary>
        /// 根据字典字段名称查询记录信息
        /// </summary>
        /// <param name="fieldName">字段名称</param>
        /// <returns>返回数据对象</returns>
        public GreatB2C.Model.Dictionary ListByFieldName( string fieldName )
        {
            string Sql = "SELECT * "
                            + " FROM "
                            + "[dbo].[" + Prefix + "Dictionary]"
                            + " WHERE "
                            + "	[FieldName] = @FieldName";

            try
            {
                SqlParameter param = new SqlParameter( "@FieldName" , SqlDbType.NVarChar , 50 );
                param.Value = fieldName;

                using ( DataTable dtData = DbHelper.ExecuteTable( CommandType.Text , Sql , param ) )
                {
                    if ( dtData.Rows.Count > 0 )
                    {
                        GreatB2C.Model.Dictionary modClass = new GreatB2C.Model.Dictionary( );

                        modClass.FieldID = Convert.ToInt32( dtData.Rows[ 0 ][ "FieldID" ].ToString( ) );
                        modClass.Title = dtData.Rows[ 0 ][ "Title" ].ToString( );
                        modClass.TableName = dtData.Rows[ 0 ][ "TableName" ].ToString( );
                        modClass.FieldName = dtData.Rows[ 0 ][ "FieldName" ].ToString( );
                        modClass.FieldValue = dtData.Rows[ 0 ][ "FieldValue" ].ToString( );

                        return modClass;
                    }
                }

                return null;
            }
            catch ( Exception ex )
            {
                Console.Write( ex.Message );
                return null;
            }
        }
        
        /// <summary>
        /// 根据字段名称更新记录
        /// </summary>
        /// <param name="objModel">相应的对象映射模型</param>
        /// <returns>操作结果：true-成功 false-失败</returns>
        public bool UpdateFieldValue( GreatB2C.Model.Dictionary objModel )
        {
            string Sql = "UPDATE "
                        + "[dbo].[" + Prefix + "Dictionary]"
                        + " SET "
                        + "[FieldValue] = @FieldValue"
                        + " WHERE "
                        + "[FieldName] = @FieldName"
                        ;

            try
            {
                int i=0;
                SqlParameter[] param = new SqlParameter[ 2 ];

                param[ i ] = new SqlParameter( "@FieldName" , SqlDbType.NVarChar , 50 );
                param[ i++ ].Value = objModel.FieldName;

                param[ i ] = new SqlParameter( "@FieldValue" , SqlDbType.NText );
                param[ i++ ].Value = objModel.FieldValue;

                int affect = DbHelper.ExecuteNonQuery( CommandType.Text , Sql , param );

                if ( affect == 1 )
                    return true;
                else
                    return false;
            }
            catch ( Exception ex )
            {
                Console.Write( ex.Message );
                return false;
            }
        }

        #endregion

        #region self-define method
        public GreatB2C.Model.Dictionary ListDictionary(string tableName, string fieldName)
        {
            string Sql = "SELECT * "
                    + " FROM "
                    + "[dbo].[" + Prefix + "Dictionary]"
                    + " WHERE "
                    + "	[FieldName] = @FieldName"
                    + " And "
                    + "[TableName] = @TableName";

            try
            {
                SqlParameter[] param = new SqlParameter[2];
                param[0] = new SqlParameter("@FieldName", SqlDbType.NVarChar, 50);
                param[0].Value = fieldName;
                param[1] = new SqlParameter("@TableName", SqlDbType.NVarChar, 50);
                param[1].Value = tableName;
                
                using (DataTable dtData = DbHelper.ExecuteTable(CommandType.Text, Sql, param))
                {
                    if (dtData.Rows.Count > 0)
                    {
                        GreatB2C.Model.Dictionary modClass = new GreatB2C.Model.Dictionary();

                        modClass.FieldID = Convert.ToInt32(dtData.Rows[0]["FieldID"].ToString());
                        modClass.Title = dtData.Rows[0]["Title"].ToString();
                        modClass.TableName = dtData.Rows[0]["TableName"].ToString();
                        modClass.FieldName = dtData.Rows[0]["FieldName"].ToString();
                        modClass.FieldValue = dtData.Rows[0]["FieldValue"].ToString();

                        return modClass;
                    }
                }

                return null;
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                return null;
            }
        }
        #endregion
    }
}
